/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbhandler;

import entityclasses.CandidateList;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

/**
 *
 * @author King
 */
public class retrieveCandidate {

    public CandidateList retrieveCandidate(int prog_id, int year) throws Exception {
        DBConnection db = new DBConnection();

        String query1 = "SELECT `tbl_elect_candidate`.`candidate_id`,`tbl_elect_candidate`.`com_id`,`tbl_elect_candidate`.`grp_id`,`tbl_users`.`fname`,`tbl_users`.`mname`,`tbl_users`.`lname`,`tbl_group`.`prog_id`,`tbl_maxrep`.`max`,`tbl_maxrep`.`year` FROM tbl_elect_candidate "
                        + "LEFT JOIN `evoting`.`tbl_users` ON `tbl_elect_candidate`.`user_id` = `tbl_users`.`user_id` "
                        + "LEFT JOIN `evoting`.`tbl_maxrep` ON `tbl_elect_candidate`.`grp_id` = `tbl_maxrep`.`grp_id` "
                        + "LEFT JOIN `evoting`.`tbl_group` ON `tbl_maxrep`.`grp_id` = `tbl_group`.`grp_id` "
                        + "where `tbl_group`.`prog_id` = " + prog_id
                        + " order by `tbl_elect_candidate`.`com_id`";

        if (db.connect()) {
            CandidateList canmap = new CandidateList();
            ArrayList<Integer> group = new ArrayList<Integer>();
            ArrayList<Integer> committee = new ArrayList<Integer>();
            ArrayList<Integer> candidate_id = new ArrayList<Integer>();
            HashMap<Integer, Integer> ht_Max = new HashMap<Integer, Integer>();
            HashMap<Integer, ArrayList<Integer>> ht_Group = new HashMap<Integer, ArrayList<Integer>>();
            HashMap<Integer, ArrayList<Integer>> ht_Candidate_Id = new HashMap<Integer, ArrayList<Integer>>();
            HashMap<Integer, String> ht_Candidate_Name = new HashMap<Integer, String>();
            HashMap<Integer, String> ht_Committee_Name = new HashMap<Integer, String>();
            ResultSet rs1 = db.customQuery(query1);
            int progCount = getCount(db, "tbl_program", "1", 1);
            int groupCount = 0;
            String s = "";
            String com = "";
            while (rs1.next()) {
                int j = rs1.getInt("tbl_elect_candidate.candidate_id");
                int com_id = rs1.getInt("tbl_elect_candidate.com_id");
                int groupid = rs1.getInt("tbl_elect_candidate.grp_id");
                if (!committee.contains(com_id)) {
                    groupCount = getCount(db, "tbl_maxrep", "com_id", com_id);
                    committee.add(com_id);
                    com += "'" + com_id + "',";
                    group = new ArrayList<Integer>();
                }
                if(!group.contains(groupid))
                {
                    if((groupCount > 1) && (prog_id == 1))
                    {
                        if(year == rs1.getInt("tbl_maxrep.year"))
                        {
                            group.add(groupid);
                            ht_Max.put(groupid, rs1.getInt("tbl_maxrep.max"));
                            candidate_id = new ArrayList<Integer>();
                        }
                        else
                            continue;
                    }
                    else
                    {
                        group.add(groupid);
                        ht_Max.put(groupid, rs1.getInt("tbl_maxrep.max"));
                        candidate_id = new ArrayList<Integer>();
                    }
                }
                for (int i = 4; i <= 6; i++) {
                    if(rs1.getString(i) != null)
                    s += rs1.getString(i) + " ";
                }
                ht_Candidate_Name.put(j, s);
                candidate_id.add(j);
                ht_Candidate_Id.put(groupid, candidate_id);
                ht_Group.put(com_id, group);
                s = "";
            }
            rs1.close();
            if(!com.equals(""))
            {
                com = "select com_id,com_name from tbl_committee where com_id in (" + com.substring(0, com.lastIndexOf(',')) + ")";
                rs1 = db.customQuery(com);
                while (rs1.next()) {
                    ht_Committee_Name.put(Integer.parseInt(rs1.getString(1)), rs1.getString(2));
                }
            }
            db.disconnect();
            System.out.println(ht_Candidate_Id);
            System.out.println(ht_Candidate_Name);
            System.out.println(ht_Committee_Name);
            System.out.println(ht_Group);
            System.out.println(ht_Max);
            System.out.println(committee);
            canmap.setHt_Candidate_Id(ht_Candidate_Id);
            canmap.setHt_Candidate_Name(ht_Candidate_Name);
            canmap.setCom_id(committee);
            canmap.setHt_Committee_Name(ht_Committee_Name);
            canmap.setHt_Group(ht_Group);
            canmap.setHt_Max(ht_Max);
            return canmap;
        }
        return null;
    }
   
    public int getCount(DBConnection db,String tbl_name,String fieldName,int com_id) throws Exception
    {
        int count = 0;
        ResultSet rs = db.customQuery("select count(*) from "+ tbl_name +" where "+ fieldName +" = " + com_id);
        if(rs.next())
        {
            count = rs.getInt(1);
        }
        return count;
    }
    
    public static void main(String[] args) throws Exception {
        new retrieveCandidate().retrieveCandidate(1, 2);
    }
}
